from typing import List, Dict, Any, Tuple
from datetime import datetime, timedelta, date
from tortoise.functions import Count
from apps.models.models import (
    User, User_Profiles, Coach, Student, Student_trainings,
    Appointments, Training_Programs
)
from apps.models.models import (
    RoleENUM, GenderENUM, Student_Training_Status,
    Appointments_Status, Rating
)


async def get_dashboard_stats() -> Dict[str, Any]:
    """获取统计数据"""
    student_count = await Student.filter(is_active=True).count()
    coach_count = await Coach.filter(is_active=True).count()

    # 本月预约数
    today = datetime.now().date()
    first_day_of_month = today.replace(day=1)
    appointment_count = await Appointments.filter(
        appointment_date__gte=first_day_of_month,
        appointment_date__lte=today
    ).count()
    # 修改后的通过率计算逻辑
    # 总数为除SCHEDULED以外的所有记录
    total_trainings = await Student_trainings.exclude(status=Student_Training_Status.SCHEDULED).count()
    # 通过数为COMPLETED状态的记录
    completed_trainings = await Student_trainings.filter(status=Student_Training_Status.COMPLETED).count()

    pass_rate = round((completed_trainings / total_trainings * 100) if total_trainings > 0 else 0, 2)

    return {
        "studentCount": student_count,
        "coachCount": coach_count,
        "appointmentCount": appointment_count,
        "passRate": pass_rate
    }


async def get_growth_data() -> Dict[str, Any]:
    """获取增长数据"""
    # 学员同比增长（与去年同期比较）
    today = datetime.now().date()
    first_day_of_month = today.replace(day=1)
    this_year_start = today.replace(month=1, day=1)
    last_year_start = this_year_start.replace(year=this_year_start.year - 1)
    last_year_end = today.replace(year=today.year - 1)

    this_year_students = await Student.filter(
        enrollment_date__gte=this_year_start,
        enrollment_date__lte=today
    ).count()

    last_year_students = await Student.filter(
        enrollment_date__gte=last_year_start,
        enrollment_date__lte=last_year_end
    ).count()

    student_growth = round(
        ((this_year_students - last_year_students) / last_year_students * 100)
        if last_year_students > 0 else 0, 2
    )

    # 教练同比增长
    this_year_coaches = await Coach.filter(
        hire_date__gte=this_year_start,
        hire_date__lte=today
    ).count()

    last_year_coaches = await Coach.filter(
        hire_date__gte=last_year_start,
        hire_date__lte=last_year_end
    ).count()

    coach_growth = round(
        ((this_year_coaches - last_year_coaches) / last_year_coaches * 100)
        if last_year_coaches > 0 else 0, 2
    )

    # 预约同比增长
    this_month_appointments = await Appointments.filter(
        appointment_date__gte=first_day_of_month,
        appointment_date__lte=today
    ).count()

    last_year_same_month_start = first_day_of_month.replace(year=first_day_of_month.year - 1)
    last_year_same_month_end = today.replace(year=today.year - 1)

    last_year_appointments = await Appointments.filter(
        appointment_date__gte=last_year_same_month_start,
        appointment_date__lte=last_year_same_month_end
    ).count()

    appointment_growth = round(
        ((this_month_appointments - last_year_appointments) / last_year_appointments * 100)
        if last_year_appointments > 0 else 0, 2
    )

    # 通过率增长（这里简化处理，实际应更复杂）
    pass_rate_growth = 0.0

    return {
        "studentGrowth": student_growth,
        "coachGrowth": coach_growth,
        "appointmentGrowth": appointment_growth,
        "passRateGrowth": pass_rate_growth
    }


async def get_student_trend() -> Dict[str, Any]:
    """获取学员增长趋势"""
    # 获取过去12个月的数据
    months = []
    student_counts = []

    today = datetime.now().date()
    for i in range(11, -1, -1):
        year = today.year
        month = today.month - i
        if month <= 0:
            month += 12
            year -= 1

        month_start = datetime(year, month, 1).date()
        if month == 12:
            month_end = datetime(year, 12, 31).date()
        else:
            month_end = datetime(year, month + 1, 1).date() - timedelta(days=1)

        count = await Student.filter(
            enrollment_date__gte=month_start,
            enrollment_date__lte=month_end
        ).count()

        months.append(f"{year}-{month:02d}")
        student_counts.append(count)

    return {
        "months": months,
        "studentCounts": student_counts
    }


async def get_coach_specialization() -> List[Dict[str, Any]]:
    """获取教练技能分布"""
    specializations = await Coach.filter(is_active=True).annotate(
        count=Count('coach_id')
    ).group_by('specialization').values('specialization', 'count')

    # 过滤掉specialization为None的记录
    result = [
        {"specialization": item["specialization"] or "未指定", "count": item["count"]}
        for item in specializations
        if item["specialization"]
    ]

    return result


async def get_appointment_status() -> Dict[str, int]:
    """获取预约状态分布"""
    status_counts = await Appointments.annotate(
        count=Count('appointment_id')
    ).group_by('status').values('status', 'count')

    result = {
        "pending": 0,
        "confirmed": 0,
        "completed": 0,
        "cancelled": 0
    }

    for item in status_counts:
        status = item["status"].value.lower()
        if status in result:
            result[status] = item["count"]

    return result


async def get_student_gender() -> Dict[str, int]:
    """获取学员性别分布"""
    # 修改关联关系，使用正确的关联路径
    profiles = await User_Profiles.filter(user_id__role=RoleENUM.STUDENT).annotate(
        count=Count('profile_id')
    ).group_by('gender').values('gender', 'count')

    result = {
        "male": 0,
        "female": 0,
        "other": 0
    }

    for item in profiles:
        gender = item["gender"].value.lower()
        if gender in result:
            result[gender] = item["count"]

    return result


async def get_student_license_type() -> List[Dict[str, Any]]:
    """获取学员驾照类型分布"""
    license_types = await Student.annotate(
        count=Count('student_id')
    ).group_by('license_type').values('license_type', 'count')

    result = [
        {"license_type": item["license_type"], "count": item["count"]}
        for item in license_types
    ]

    return result


async def get_coach_rating() -> Dict[str, int]:
    """获取教练评分分布"""
    ratings = await Coach.filter(is_active=True).annotate(
        count=Count('coach_id')
    ).group_by('rating').values('rating', 'count')

    result = {
        "one": 0,
        "two": 0,
        "three": 0,
        "four": 0,
        "five": 0
    }

    for item in ratings:
        rating_key = Rating(item["rating"]).name.lower()
        if rating_key in result:
            result[rating_key] = item["count"]

    return result


async def get_student_progress() -> Dict[str, int]:
    """获取学员学习进度分布"""
    # 定义进度范围
    ranges = [
        ("range1", 0, 20),
        ("range2", 20, 40),
        ("range3", 40, 60),
        ("range4", 60, 80),
        ("range5", 80, 100)
    ]

    result = {}
    for range_key, min_val, max_val in ranges:
        if max_val == 100:
            count = await Student.filter(
                progress_percentage__gte=min_val,
                progress_percentage__lte=max_val
            ).count()
        else:
            count = await Student.filter(
                progress_percentage__gte=min_val,
                progress_percentage__lt=max_val
            ).count()
        result[range_key] = count

    return result


async def get_coach_active() -> Dict[str, int]:
    """获取教练活跃状态"""
    active_count = await Coach.filter(is_active=True).count()
    inactive_count = await Coach.filter(is_active=False).count()

    return {
        "active": active_count,
        "inactive": inactive_count
    }


async def get_program_active() -> Dict[str, int]:
    """获取训练项目活跃状态"""
    active_count = await Training_Programs.filter(is_active=True).count()
    inactive_count = await Training_Programs.filter(is_active=False).count()

    return {
        "active": active_count,
        "inactive": inactive_count
    }


async def get_training_status() -> Dict[str, int]:
    """获取学员训练状态分布"""
    status_counts = await Student_trainings.annotate(
        count=Count('training_id')
    ).group_by('status').values('status', 'count')

    result = {
        "scheduled": 0,
        "completed": 0,
        "canceled": 0,
        "missed": 0
    }

    for item in status_counts:
        status = item["status"].value.lower()
        if status in result:
            result[status] = item["count"]

    return result


async def get_recent_students() -> List[Dict[str, Any]]:
    """获取最新学员"""
    # 修改关联关系字段，使用正确的 profiles 而不是 user__profiles
    students = await Student.filter(is_active=True).limit(5)
    result = []
    for student in students:
        user = await User.filter(user_id=student.user_id_id)
        userId = user[0].user_id
        profile = await User_Profiles.get_or_none(user_id_id=userId)
        result.append(
            {
                "fullname": profile.fullname if profile else "未知",
                "enrollment_date": student.enrollment_date,
                "status": "学习中" if student.is_active else "已毕业"
            }
        )
    return result


async def get_recent_appointments() -> List[Dict[str, Any]]:
    """获取最新预约"""
    appointments = await Appointments.all().prefetch_related(
        'student_id__user_id__profiles',
        'coach_id__user_id__profiles'
    ).order_by('-create_at').limit(5)

    result = []
    for appointment in appointments:
        # 直接通过预加载的关系获取用户名
        # 通过预加载的关系获取用户全名
        student_profiles = list(appointment.student_id.user_id.profiles) if hasattr(
            appointment.student_id.user_id.profiles, '__iter__') else [appointment.student_id.user_id.profiles]
        coach_profiles = list(appointment.coach_id.user_id.profiles) if hasattr(appointment.coach_id.user_id.profiles,
                                                                                '__iter__') else [
            appointment.coach_id.user_id.profiles]

        student_fullname = student_profiles[0].fullname if student_profiles and student_profiles[0] else "未知学员"
        coach_fullname = coach_profiles[0].fullname if coach_profiles and coach_profiles[0] else "未知教练"

        result.append({
                "student_name": student_fullname,
                "coach_name": coach_fullname,
                "appointment_date": appointment.appointment_date.strftime("%Y-%m-%d"),
                "status": appointment.status.value
            })

    return result
